﻿Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Data.SqlTypes
Imports System.Collections
Imports System.Collections.Generic
Imports System.Data.Common
Public Class Connection
    Private sConnVSM As SqlConnection
    Private sConnDealer As SqlConnection
    Private conStringVSM As String
    Private conStringDealer As String
    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="dbType"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function setDB(ByVal dbType As String) As String
        Try
            Dim DatabaseName, SAPUserID, SAPPassWord, SQLUser, SQLPwd, SQLServer, LicenseServer As String
            Dim strConnect As String = String.Empty
            Dim sCon As String = String.Empty
            Dim SQLType As String = String.Empty
            Dim MyArr As Array
            Dim sErrMsg As String = String.Empty
            If dbType = PublicVariable.VSM Then
                strConnect = "DBConnectVSM"
            ElseIf dbType = PublicVariable.DEALER Then
                strConnect = "DBConnectDealer"
            End If
            sCon = System.Configuration.ConfigurationManager.AppSettings.Get(strConnect)
            MyArr = sCon.Split(";")
            If MyArr.Length > 0 Then

                DatabaseName = MyArr(0).ToString()
                SAPUserID = MyArr(1).ToString()
                SAPPassWord = MyArr(2).ToString()
                SQLServer = MyArr(3).ToString()
                SQLUser = MyArr(4).ToString()
                SQLPwd = MyArr(5).ToString()
                LicenseServer = MyArr(6).ToString()

                sCon = "server= " + SQLServer + ";database=" + DatabaseName + " ;uid=" + SQLUser + "; pwd=" + SQLPwd + ";"

                If dbType = PublicVariable.VSM Then

                    conStringVSM = sCon
                    sConnVSM = New SqlConnection(sCon)

                    If IsNothing(PublicVariable.mVSMDataComapny) Then
                        PublicVariable.mVSMDataComapny = New SAPbobsCOM.Company
                    End If

                    PublicVariable.mVSMDataComapny.CompanyDB = DatabaseName
                    PublicVariable.mVSMDataComapny.UserName = SAPUserID
                    PublicVariable.mVSMDataComapny.Password = SAPPassWord
                    PublicVariable.mVSMDataComapny.Server = SQLServer
                    PublicVariable.mVSMDataComapny.DbUserName = SQLUser
                    PublicVariable.mVSMDataComapny.DbPassword = SQLPwd
                    PublicVariable.mVSMDataComapny.LicenseServer = LicenseServer
                    PublicVariable.mVSMDataComapny.DbServerType = SAPbobsCOM.BoDataServerTypes.dst_MSSQL2008
                  
                ElseIf dbType = PublicVariable.DEALER Then

                    conStringDealer = sCon
                    sConnDealer = New SqlConnection(sCon)

                    If IsNothing(PublicVariable.mDEADataComapny) Then
                        PublicVariable.mDEADataComapny = New SAPbobsCOM.Company
                    End If

                    PublicVariable.mDEADataComapny.CompanyDB = DatabaseName
                    PublicVariable.mDEADataComapny.UserName = SAPUserID
                    PublicVariable.mDEADataComapny.Password = SAPPassWord
                    PublicVariable.mDEADataComapny.Server = SQLServer
                    PublicVariable.mDEADataComapny.DbUserName = SQLUser
                    PublicVariable.mDEADataComapny.DbPassword = SQLPwd
                    PublicVariable.mDEADataComapny.LicenseServer = LicenseServer
                    PublicVariable.mDEADataComapny.DbServerType = SAPbobsCOM.BoDataServerTypes.dst_MSSQL2008

                    End If
                End If
        Catch ex As Exception
            Utils.WriteLog(ex.ToString)
        End Try
        Return String.Empty
    End Function
    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="dbType"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function connectDB(ByVal dbType As String) As String
        Try
            Dim sErrMsg As String = String.Empty
            Dim connectOk As Integer = 0
            setDB(dbType)
            If dbType = PublicVariable.VSM Then
                If PublicVariable.mVSMDataComapny.Connected = False Then
                    If PublicVariable.mVSMDataComapny.Connect() <> 0 Then
                        PublicVariable.mVSMDataComapny.GetLastError(connectOk, sErrMsg)
                        Return sErrMsg
                    Else
                        Return String.Empty
                    End If
                End If
            ElseIf dbType = PublicVariable.DEALER Then
                If PublicVariable.mDEADataComapny.Connected = False Then
                    If PublicVariable.mDEADataComapny.Connect() <> 0 Then
                        PublicVariable.mDEADataComapny.GetLastError(connectOk, sErrMsg)
                        Return sErrMsg
                    Else
                        Return String.Empty
                    End If
                End If
            End If
        Catch ex As Exception
            Return ex.ToString
        End Try
        Return String.Empty
    End Function
    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="dbType"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function GetConnectionString(ByVal dbType As String) As SqlConnection
        Try
            If dbType = PublicVariable.DEALER Then
                If sConnDealer.State = ConnectionState.Open Then
                    sConnDealer.Close()
                End If
                If sConnDealer.ConnectionString.Length = 0 Then
                    sConnDealer.ConnectionString = conStringDealer
                End If
                sConnDealer.Open()
                Return sConnDealer
            ElseIf dbType = PublicVariable.VSM Then
                If sConnVSM.State = ConnectionState.Open Then
                    sConnVSM.Close()
                End If
                If sConnVSM.ConnectionString.Length = 0 Then
                    sConnVSM.ConnectionString = conStringVSM
                End If
                sConnVSM.Open()
                Return sConnVSM
            End If
        Catch ex As Exception
            Utils.WriteLog(ex.ToString)
        End Try
        Return Nothing
    End Function
    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="QueryString"></param>
    ''' <param name="dbType"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function ObjectGetAll_Query(ByVal QueryString As String, ByVal dbType As String) As DataSet
        Try
            Using myConn = GetConnectionString(dbType)
                Dim MyCommand As SqlCommand = New SqlCommand(QueryString, myConn)
                MyCommand.CommandType = CommandType.Text
                Dim da As SqlDataAdapter = New SqlDataAdapter()
                Dim mytable As DataSet = New DataSet()
                da.SelectCommand = MyCommand
                da.Fill(mytable)
                myConn.Close()
                Return mytable
            End Using
        Catch ex As Exception
            Utils.WriteLog(ex.ToString)
            Return Nothing
        End Try
    End Function
    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="QueryString"></param>
    ''' <param name="ParamArrays"></param>
    ''' <param name="dbType"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function ObjectGetAll_Query(ByVal QueryString As String, ByVal ParamArrays As Array, ByVal dbType As String) As DataSet
        Try
            Using myConn = GetConnectionString(dbType)
                Dim MyCommand As SqlCommand = New SqlCommand(QueryString, myConn)
                MyCommand.CommandType = CommandType.Text
                Dim da As SqlDataAdapter = New SqlDataAdapter()
                Dim mytable As DataSet = New DataSet()
                da.SelectCommand = MyCommand
                For i As Integer = 0 To ParamArrays.Length - 1
                    MyCommand.Parameters.AddWithValue(String.Format("{0}{1}", "@Param", i + 1), ParamArrays(i))
                Next
                da.Fill(mytable)
                myConn.Close()
                Return mytable
            End Using
        Catch ex As Exception
            Utils.WriteLog(ex.ToString)
            Return Nothing
        End Try
    End Function

    Public Function ObjectGetAll_Stored_SAP(ByVal storedName As String, ByVal ParamArrays As DataSet, ByVal dbType As String) As DataSet
        Try
            Using myConn = GetConnectionString(dbType)
                Dim MyCommand As SqlCommand = New SqlCommand(storedName, myConn)
                MyCommand.CommandType = CommandType.StoredProcedure

                Dim da As SqlDataAdapter = New SqlDataAdapter()
                Dim mytable As DataSet = New DataSet()
                da.SelectCommand = MyCommand
                For Each dr As DataRow In ParamArrays.Tables(0).Rows
                    MyCommand.Parameters.AddWithValue(dr("key").ToString(), dr("value"))
                Next
                da.Fill(mytable)
                myConn.Close()
                Return mytable
            End Using
        Catch ex As Exception
            Utils.WriteLog(ex.ToString)
            Return Nothing
        End Try
    End Function

    Public Function ObjectGetAll_Stored_SAP(ByVal storedName As String, ByVal ParamKey As String, ByVal ParamValue As String, ByVal dbType As String) As DataSet
        Try
            Using myConn = GetConnectionString(dbType)
                Dim MyCommand As SqlCommand = New SqlCommand(storedName, myConn)
                MyCommand.CommandType = CommandType.StoredProcedure

                Dim da As SqlDataAdapter = New SqlDataAdapter()
                Dim mytable As DataSet = New DataSet()
                da.SelectCommand = MyCommand
                MyCommand.Parameters.AddWithValue(ParamKey, ParamValue)
                da.Fill(mytable)
                myConn.Close()
                Return mytable
            End Using
        Catch ex As Exception
            Utils.WriteLog(ex.ToString)
            Return Nothing
        End Try
    End Function

    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="Query"></param>
    ''' <param name="ParamArrays"></param>
    ''' <param name="dbType"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function Object_Execute(ByVal Query As String, ByVal ParamArrays As Array, ByVal dbType As String) As Integer
        Try
            Using myConn = GetConnectionString(dbType)
                Dim MyCommand As SqlCommand = New SqlCommand(Query, myConn)
                MyCommand.CommandType = CommandType.Text
                For i As Integer = 0 To ParamArrays.Length - 1
                    MyCommand.Parameters.AddWithValue(String.Format("{0}{1}", "@Param", i + 1), ParamArrays(i))
                Next
                Dim count As Integer = MyCommand.ExecuteNonQuery()
                myConn.Close()
                Return count
            End Using
        Catch ex As Exception
            Utils.WriteLog(ex.ToString)
            Return 0
        End Try
    End Function
    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="Query"></param>
    ''' <param name="dbType"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function Object_Execute(ByVal Query As String, ByVal dbType As String) As Integer
        Try
            Using myConn = GetConnectionString(dbType)
                Dim MyCommand As SqlCommand = New SqlCommand(Query, myConn)
                MyCommand.CommandType = CommandType.Text
                Dim count As Integer = MyCommand.ExecuteNonQuery()
                myConn.Close()
                Return count
            End Using
        Catch ex As Exception
            Utils.WriteLog(ex.ToString)
            Return 0
        End Try
    End Function
    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="connection"></param>
    ''' <param name="commandType"></param>
    ''' <param name="commandText"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function CreateCommand(ByVal connection As SqlConnection, ByVal commandType As CommandType, ByVal commandText As String) As SqlCommand
        Using command As New SqlCommand()
            command.Connection = connection
            command.CommandText = commandText
            command.CommandType = commandType
            Return command
        End Using
    End Function
    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="connection"></param>
    ''' <param name="commandType"></param>
    ''' <param name="commandText"></param>
    ''' <param name="values"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function CreateCommand(ByVal connection As SqlConnection, ByVal commandType As CommandType, ByVal commandText As String, ByVal ParamArray values As Object()) As SqlCommand
        Using command As New SqlCommand()
            command.Connection = connection
            command.CommandText = commandText
            command.CommandType = commandType
            If (values Is Nothing) OrElse (values.Length = 0) Then
                For j As Integer = 0 To command.Parameters.Count - 1
                    command.Parameters(j).Value = DBNull.Value
                Next
                Return command
            End If
            command.Parameters.AddRange(values)
            Return command
        End Using
    End Function
    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="connection"></param>
    ''' <param name="commandType"></param>
    ''' <param name="commandText"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function ExecuteDataSet(ByVal connection As SqlConnection, ByVal commandType As CommandType, ByVal commandText As String) As DataSet
        Return ExecuteDataSet(connection, commandType, commandText, DirectCast(Nothing, DbParameter()))
    End Function
    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="connection"></param>
    ''' <param name="commandType"></param>
    ''' <param name="commandText"></param>
    ''' <param name="parameters"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function ExecuteDataSet(ByVal connection As SqlConnection, ByVal commandType As CommandType, ByVal commandText As String, ByVal ParamArray parameters As DbParameter()) As DataSet
        If System.Transactions.Transaction.Current Is Nothing Then
            Using command As SqlCommand = CreateCommand(connection, commandType, commandText, parameters)
                Return SQLHelper.CreateDataSet(command)
            End Using
        End If
        Using command2 As SqlCommand = CreateCommand(connection, commandType, commandText, parameters)
            Return SQLHelper.CreateDataSet(command2)
        End Using
    End Function
    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="dbType"></param>
    ''' <param name="commandType"></param>
    ''' <param name="commandText"></param>
    ''' <remarks></remarks>
    Public Sub ExecuteNonQuery(ByVal dbType As String, ByVal commandType As CommandType, ByVal commandText As String)
        ExecuteNonQuery(dbType, commandType, commandText, DirectCast(Nothing, DbParameter()))
    End Sub
    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="dbType"></param>
    ''' <param name="commandType"></param>
    ''' <param name="commandText"></param>
    ''' <param name="parameters"></param>
    ''' <remarks></remarks>
    Public Sub ExecuteNonQuery(ByVal dbType As String, ByVal commandType As CommandType, ByVal commandText As String, ByVal ParamArray parameters As DbParameter())
        Using Connection = GetConnectionString(dbType)
            If System.Transactions.Transaction.Current Is Nothing Then
                Using command As SqlCommand = CreateCommand(Connection, commandType, commandText, parameters)
                    Try
                        command.ExecuteNonQuery()
                    Catch sqlEx As SqlException
                        Throw sqlEx
                    End Try
                End Using
                Return
            End If
            Using command2 As SqlCommand = CreateCommand(Connection, commandType, commandText, parameters)
                command2.ExecuteNonQuery()
            End Using
        End Using
    End Sub
    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="dbType"></param>
    ''' <param name="commandType"></param>
    ''' <param name="commandText"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function ExecuteReader(ByVal dbType As String, ByVal commandType As CommandType, ByVal commandText As String) As IDataReader
        Return ExecuteReader(dbType, commandType, commandText, DirectCast(Nothing, DbParameter()))
    End Function
    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="dbType"></param>
    ''' <param name="commandType"></param>
    ''' <param name="commandText"></param>
    ''' <param name="parameters"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function ExecuteReader(ByVal dbType As String, ByVal commandType As CommandType, ByVal commandText As String, ByVal ParamArray parameters As DbParameter()) As IDataReader
        Using Connection = GetConnectionString(dbType)
            If System.Transactions.Transaction.Current Is Nothing Then
                Using command As SqlCommand = CreateCommand(Connection, commandType, commandText, parameters)
                    Return command.ExecuteReader(CommandBehavior.CloseConnection)
                End Using
            End If
            Using command2 As SqlCommand = CreateCommand(Connection, commandType, commandText, parameters)
                Return command2.ExecuteReader()
            End Using
        End Using
    End Function
    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="dbType"></param>
    ''' <param name="commandType"></param>
    ''' <param name="commandText"></param>
    ''' <param name="parameters"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function ExecuteScalar(ByVal dbType As String, ByVal commandType As CommandType, ByVal commandText As String, ByVal ParamArray parameters As DbParameter()) As Object
        Using connection = GetConnectionString(dbType)
            If System.Transactions.Transaction.Current Is Nothing Then
                Using command As SqlCommand = CreateCommand(connection, commandType, commandText, parameters)
                    Return command.ExecuteScalar()
                End Using
            End If
            Using command2 As SqlCommand = CreateCommand(connection, commandType, commandText, parameters)
                Return command2.ExecuteScalar()
            End Using
        End Using
    End Function
    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="sqla"></param>
    ''' <param name="dbType"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function ExecuteQuery(ByVal sqla As ArrayList, ByVal dbType As String) As Boolean
        Using connection = GetConnectionString(dbType)
            Dim flag As Boolean = True

            If System.Transactions.Transaction.Current Is Nothing Then
                Dim transaction As SqlTransaction = connection.BeginTransaction()
                Dim command As SqlCommand = connection.CreateCommand()
                command.Connection = connection
                command.Transaction = transaction
                Try
                    ' convert sql ArrayList to sql string array
                    Dim sql As String() = New String(sqla.Count - 1) {}
                    For i As Integer = 0 To sqla.Count - 1
                        sql(i) = sqla(i).ToString()
                    Next
                    ' ExcuteNonQuery
                    For i As Integer = 0 To sql.Length - 1
                        command.CommandText = sql(i)
                        command.ExecuteNonQuery()
                    Next
                    transaction.Commit()
                Catch exception As SqlException
                    Utils.WriteLog(exception.Message)
                    PublicVariable.ErrMsg = exception.Message
                    flag = False
                    transaction.Rollback()
                Catch exception2 As Exception
                    Utils.WriteLog(exception2.Message)
                    PublicVariable.ErrMsg = exception2.Message
                    flag = False
                    transaction.Rollback()
                End Try
                transaction = Nothing
                command = Nothing
            End If
            Return flag
        End Using
    End Function
End Class
